CREATE DATABASE wsdb_db
GO
ALTER DATABASE wsdb_db SET TRUSTWORTHY ON
GO
USE wsdb_db
GO
CREATE SCHEMA i
GO
CREATE PROCEDURE i.METHODS_vw
(@wsschema nvarchar(128))
AS

SET NOCOUNT ON

DECLARE @schemaid int

SET @schemaid=SCHEMA_ID(@wsschema)

SELECT 
  OBJECT_NAME(object_id) as method,
  SCHEMA_NAME(schema_id)+'.'+OBJECT_NAME(object_id) as fqdn,
  schema_id as schemaid,
  object_id as objectid,
  CASE WHEN RIGHT(OBJECT_NAME(object_id),3)='_vw' THEN 1 ELSE 0 END as hasdataset
FROM sys.objects
WHERE schema_id=@schemaid and type='P'
ORDER BY 1
GO
CREATE PROCEDURE i.METHOD_PARAMS_vw
(@objectid int)
AS

SET NOCOUNT ON

SELECT 
  sp.name as paramname,
  sp.parameter_id as pos,
  TYPE_NAME(sp.system_type_id) as systype,
  TYPE_NAME(sp.user_type_id) as udtname,
  COLUMNPROPERTY(sp.object_id, sp.name, 'charmaxlen') as datalen,
  CONVERT(tinyint,sp.is_output) as isoutput,
  sp.precision as numprecis,
  sp.scale as numscale,
  CASE WHEN TYPE_NAME(sp.user_type_id) in ('ebool','bool','boolean') THEN 1 ELSE 0 END as bool,
  CASE WHEN TYPE_NAME(sp.system_type_id)='table type' THEN 1 ELSE 0 END as dataset
FROM sys.parameters sp WITH(NOLOCK)
WHERE object_id=@objectid
ORDER BY sp.parameter_id
GO
CREATE ROLE r_wsdb
GO
GRANT EXEC ON SCHEMA::i TO r_wsdb
GO
CREATE LOGIN wsdbuser WITH PASSWORD='wsdbpassword'
GO
CREATE USER wsdbuser FOR LOGIN wsdbuser
GO
EXEC sp_addrolemember 'r_wsdb','wsdbuser'
GO
CREATE SCHEMA ws
GO
CREATE PROCEDURE ws.TEST1
(@x int,
 @y int OUTPUT)
AS

SET NOCOUNT ON

SET @y = @x+1
GO
CREATE PROCEDURE ws.TEST2_vw
(@id int)
AS

SET NOCOUNT ON

SELECT @id as id, 'a' as name
UNION
SELECT @id+1, 'b' as name
UNION
SELECT @id+2, 'c' as name
GO
GRANT EXEC ON schema::ws TO r_wsdb
